CREATE TABLE [dbo].[SourceCode]
(
[SourceCodeKey] [uniqueidentifier] NOT NULL,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Code] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SourceCodeTypeCode] [int] NOT NULL CONSTRAINT [DF_SourceCode_SourceCodeTypeCode] DEFAULT ((0)),
[CostCollection] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OverheadCost] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_SourceCode_OverheadCost] DEFAULT ((0)),
[EstimatedCost] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_SourceCode_EstimatedCost] DEFAULT ((0)),
[TargetRevenue] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_SourceCode_TargetRevenue] DEFAULT ((0)),
[TotalRevenue] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_SourceCode_TotalRevenue] DEFAULT ((0)),
[TotalSolicited] [int] NOT NULL CONSTRAINT [DF_SourceCode_TotalSolicited] DEFAULT ((0)),
[PredictedResponseRate] [decimal] (5, 4) NOT NULL CONSTRAINT [DF_SourceCode_PredictedResponseRate] DEFAULT ((0)),
[PackageKey] [uniqueidentifier] NULL,
[ExternalListCount] [int] NOT NULL CONSTRAINT [DF_SourceCode_ExternalListCount] DEFAULT ((0)),
[ExternalFileName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_SourceCode_ExternalFileName] DEFAULT (''),
[SourceCodeStatusCode] [int] NOT NULL CONSTRAINT [DF_SourceCode_SourceCodeStatusCode] DEFAULT ((0)),
[ResponsesTotalledOn] [datetime] NULL,
[LastDropDate] [datetime] NULL,
[FirstResponseDate] [datetime] NULL,
[LastResponseDate] [datetime] NULL,
[LowResponseAmount] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_SourceCode_LowResponseAmount] DEFAULT ((0)),
[HighResponseAmount] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_SourceCode_HighResponseAmount] DEFAULT ((0)),
[TotalPositiveResponse] [int] NOT NULL CONSTRAINT [DF_SourceCode_TotalPositiveResponse] DEFAULT ((0)),
[TotalNegativeResponse] [int] NOT NULL CONSTRAINT [DF_SourceCode_TotalNegativeResponse] DEFAULT ((0)),
[UpdatedOn] [datetime] NOT NULL,
[UpdatedByUserKey] [uniqueidentifier] NOT NULL,
[CreatedByUserKey] [uniqueidentifier] NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[SolicitationKey] [uniqueidentifier] NULL,
[MarkedForDeleteOn] [datetime] NULL,
[AccessKey] [uniqueidentifier] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
CREATE TRIGGER [dbo].[asi_SourceCodeDelete]
   ON  [dbo].[SourceCode]
   AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DELETE FROM Gen_Tables
    WHERE CODE IN (Select Code From deleted)
    AND TABLE_NAME = 'SOURCE_CODE'
    
    
    -- This is an Appeal too
    DELETE Appeal
    WHERE APPEAL_CODE IN (SELECT Code from deleted)
END

GO
CREATE TRIGGER [dbo].[asi_SourceCodeInsert]
   ON  [dbo].[SourceCode]
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Gen_Tables (CODE,UPPER_CODE,DESCRIPTION,TABLE_NAME)
    SELECT Code,Code,IsNULL(Description,''),'SOURCE_CODE' FROM inserted
    
    -- This is an Appeal too

    INSERT INTO Appeal (APPEAL_CODE,TITLE,DESCRIPTION,APPEAL_TYPE,CAMPAIGN_CODE)
    SELECT i.Code,i.Code,IsNULL(i.Description,''),'',c.LegacyCampaignCode
    FROM inserted i
    INNER JOIN SourceCodeTypeRef sctr ON i.SourceCodeTypeCode = sctr.SourceCodeTypeCode
    INNER JOIN SolicitationMain s ON i.SolicitationKey = s.SolicitationKey
    INNER JOIN AppealMain a ON s.AppealKey = a.AppealKey
    INNER JOIN CampaignMain c ON a.CampaignKey = c.CampaignKey
END

GO
CREATE  TRIGGER [dbo].[asi_SourceCodeRevenueDelete]
    ON [dbo].[SourceCode]
    FOR DELETE
AS
BEGIN
UPDATE    sm1
   SET    sm1.TotalRevenue = sm1.TotalRevenue - deleted.TotalRevenue
  FROM    SolicitationMain sm1
    INNER JOIN vBoSourceCode sc1 ON sm1.SolicitationKey = sc1.SolicitationKey
    INNER JOIN deleted ON sc1.SourceCodeKey = deleted.SourceCodeKey
END

GO
CREATE  TRIGGER [dbo].[asi_SourceCodeRevenueUpdate]
    ON [dbo].[SourceCode]
    FOR INSERT, UPDATE
AS
BEGIN
UPDATE    sm1
   SET    sm1.TotalRevenue = Coalesce(
        (SELECT    Sum(sc2.TotalRevenue)
         FROM    SolicitationMain sm2
                INNER JOIN vBoSourceCode sc2 ON sm2.SolicitationKey = sc2.SolicitationKey
         WHERE    sm2.SolicitationKey = sm1.SolicitationKey), 0)
  FROM    SolicitationMain sm1
        INNER JOIN vBoSourceCode sc1 ON sm1.SolicitationKey = sc1.SolicitationKey
        INNER JOIN inserted ON sc1.SourceCodeKey = inserted.SourceCodeKey
END

GO
CREATE TRIGGER [dbo].[asi_SourceCodeUpdate]
   ON  [dbo].[SourceCode]
   AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF (UPDATE (Code) OR UPDATE (Description))
    BEGIN
       UPDATE Gen_Tables
       SET CODE = i.Code,
              UPPER_CODE = i.Code,
           DESCRIPTION = ISNULL(i.Description, '')
         FROM inserted i, deleted d
         WHERE Gen_Tables.CODE = d.Code AND (Gen_Tables.TABLE_NAME = 'SOURCE_CODE')
    
       UPDATE Appeal
       Set APPEAL_CODE = i.Code,
           TITLE = i.Code,
           DESCRIPTION = ISNULL(i.Description, ''),
           APPEAL_TYPE = '' ,
           CAMPAIGN_CODE = c.LegacyCampaignCode
           FROM inserted i, deleted d
             INNER JOIN SourceCodeTypeRef sctr ON d.SourceCodeTypeCode = sctr.SourceCodeTypeCode
             INNER JOIN SolicitationMain s ON d.SolicitationKey = s.SolicitationKey
             INNER JOIN AppealMain a ON s.AppealKey = a.AppealKey
             INNER JOIN CampaignMain c ON a.CampaignKey = c.CampaignKey
           WHERE APPEAL_CODE = d.Code
    END
    
END

GO
ALTER TABLE [dbo].[SourceCode] ADD CONSTRAINT [PK_SourceCode] PRIMARY KEY CLUSTERED ([SourceCodeKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SourceCode_AccessKey] ON [dbo].[SourceCode] ([AccessKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SourceCode_CreatedByUserKey] ON [dbo].[SourceCode] ([CreatedByUserKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SourceCode_PackageKey] ON [dbo].[SourceCode] ([PackageKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SourceCode_SolicitationKey] ON [dbo].[SourceCode] ([SolicitationKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SourceCode_SourceCodeStatusCode] ON [dbo].[SourceCode] ([SourceCodeStatusCode]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SourceCode_SourceCodeTypeCode] ON [dbo].[SourceCode] ([SourceCodeTypeCode]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SourceCode_UpdatedByUserKey] ON [dbo].[SourceCode] ([UpdatedByUserKey]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SourceCode] ADD CONSTRAINT [FK_SourceCode_AccessMain] FOREIGN KEY ([AccessKey]) REFERENCES [dbo].[AccessMain] ([AccessKey])
GO
ALTER TABLE [dbo].[SourceCode] ADD CONSTRAINT [FK_SourceCode_PackageMain] FOREIGN KEY ([PackageKey]) REFERENCES [dbo].[PackageMain] ([PackageKey])
GO
ALTER TABLE [dbo].[SourceCode] ADD CONSTRAINT [FK_SourceCode_SolicitationMain] FOREIGN KEY ([SolicitationKey]) REFERENCES [dbo].[SolicitationMain] ([SolicitationKey]) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SourceCode] ADD CONSTRAINT [FK_SourceCode_SourceCodeStatusRef] FOREIGN KEY ([SourceCodeStatusCode]) REFERENCES [dbo].[SourceCodeStatusRef] ([SourceCodeStatusCode])
GO
ALTER TABLE [dbo].[SourceCode] ADD CONSTRAINT [FK_SourceCode_SourceCodeTypeRef] FOREIGN KEY ([SourceCodeTypeCode]) REFERENCES [dbo].[SourceCodeTypeRef] ([SourceCodeTypeCode])
GO
ALTER TABLE [dbo].[SourceCode] ADD CONSTRAINT [FK_SourceCode_UniformRegistry] FOREIGN KEY ([SourceCodeKey]) REFERENCES [dbo].[UniformRegistry] ([UniformKey])
GO
ALTER TABLE [dbo].[SourceCode] ADD CONSTRAINT [FK_SourceCode_UserMain_CreatedBy] FOREIGN KEY ([CreatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO
ALTER TABLE [dbo].[SourceCode] ADD CONSTRAINT [FK_SourceCode_UserMain_UpdatedBy] FOREIGN KEY ([UpdatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO